Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# PERCENT_RANK

Aggregate Syntax

percent_rank_aggregate::=

Description of the illustration percent_rank_aggregate.gif

Analytic Syntax

percent_rank_analytic::=

Description of the illustration percent_rank_analytic.gif

See Also: "Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

`PERCENT_RANK` is similar to the `CUME_DIST` (cumulative distribution) function. The range of values returned by `PERCENT_RANK` is 0 to 1, inclusive. The first row in any set has a `PERCENT_RANK` of 0. The return value is `NUMBER`.

See Also: Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

- As an aggregate function, `PERCENT_RANK` calculates, for a hypothetical row `r` identified by the arguments of the function and a corresponding sort specification, the rank of row `r` minus 1 divided by the number of rows in the aggregate group. This calculation is made as if the hypothetical row `r` were inserted into the group of rows over which Oracle Database is to aggregate. The arguments of the function identify a single hypothetical row within each aggregate group. Therefore, they must all evaluate to constant expressions within each aggregate group. The constant argument expressions and the expressions in the `ORDER` `BY` clause of the aggregate match by position. Therefore the number of arguments must be the same and their types must be compatible.

- As an analytic function, for a row `r`, `PERCENT_RANK` calculates the rank of `r` minus 1, divided by 1 less than the number of rows being evaluated (the entire query result set or a partition).

Aggregate Example

The following example calculates the percent rank of a hypothetical employee in the sample table `hr.employees` with a salary of $15,500 and a commission of 5%:

    SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
       (ORDER BY salary, commission_pct) "Percent-Rank"
       FROM employees;

    Percent-Rank
    ------------
      .971962617

Analytic Example

The following example calculates, for each employee, the percent rank of the employee's salary within the department:

    SELECT department_id, last_name, salary,
       PERCENT_RANK()
          OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
       FROM employees
       ORDER BY pr, salary;

    DEPARTMENT_ID LAST_NAME                     SALARY         PR
    ------------- ------------------------- ---------- ----------
               10 Whalen                          4400          0
               40 Marvis                          6500          0
    . . .
               80 Vishney                        10500 .176470588
               50 Everett                         3900 .181818182
               30 Khoo                            3100         .2
    . . .
               80 Johnson                         6200 .941176471
               50 Markle                          2200 .954545455
               50 Philtanker                      2200 .954545455
               50 Olson                           2100          1
    . . .